package com.lihui.demo;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 功能描述
 *
 * @author wileda
 * @date 2022/12/26  16:02
 */
public class SelectDemo {
    public static void main(String[] args) {
        //1.创建数据源
        DataSource dataSource = new MysqlDataSource();

        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://localhost:3306/java_database?characterEncoding=UTF-8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");


        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            //开始连接数据库
            connection = dataSource.getConnection();
            //让用户动态输入学号
            Scanner sc = new Scanner(System.in);
            System.out.print("请输入学号：");
            String inputSn = sc.nextLine();
            //构造sql语句
            String sql = "select * from student where sn = ' " + inputSn + "'";
            //获取了个处理sql的PreparedStatement对象
            statement = connection.prepareStatement(sql);
            //通过statement对象执行sql语句并获取返回结果
            resultSet = statement.executeQuery();
            //遍历resultSet结果集，获取每一行记录的每一个字段
            if (resultSet.next()) {
                //学生ID
                int id = resultSet.getInt(1);
                //学号
                String sn = resultSet.getString(2);
                //姓名
                String name = resultSet.getString(3);
                //邮箱
                String mail = resultSet.getString(4);
                //班级编号
                int classesId = resultSet.getInt(5);
                //结果
                System.out.println("Id = " + id + ", 学号 = " + sn + ", 姓名 = " + name + ", 邮箱 = " + mail + ", 班级编号 = " + classesId);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
